﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FisheryPlatform.Entity;
using System.Data.SqlClient;
using System.Data;
using FisheryPlatform.DataAccess.DBUtility;

namespace FisheryPlatform.DataAccess.Option
{
    public class OptionsDB
    {
        #region private const
        private const string PARAM_OPTION_ID = "@OptionId";
        private const string PARAM_NAME = "@Name";
        private const string PARAM_VALUE = "@Value";
        private const string PARAM_AUTO_LOAD = "@AutoLoad";

        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillEntityFrom(SqlDataReader reader, OptionsEntity obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.OptionId = reader.IsDBNull(reader.GetOrdinal("OptionId")) ? 0 : reader.GetInt64(reader.GetOrdinal("OptionId"));
                obj.Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? String.Empty : reader.GetString(reader.GetOrdinal("Name"));
                obj.Value = reader.IsDBNull(reader.GetOrdinal("Value")) ? String.Empty : reader.GetString(reader.GetOrdinal("Value"));
                obj.AutoLoad = reader.IsDBNull(reader.GetOrdinal("AutoLoad")) ? Convert.ToByte(0) : reader.GetByte(reader.GetOrdinal("AutoLoad"));
            }
            #endregion
        }

        private static SqlParameter[] GetOptionsParams(OptionsEntity obj)
        {
            #region
            SqlParameter[] dbParams ={					
					 SQLHelper.MakeParam(PARAM_OPTION_ID, SqlDbType.BigInt,0,obj.OptionId),
					 SQLHelper.MakeParam(PARAM_NAME, SqlDbType.VarChar, 255,obj.Name.Trim().ToLower()),					
					 SQLHelper.MakeParam(PARAM_VALUE, SqlDbType.Text, 0,obj.Value),					
					 SQLHelper.MakeParam(PARAM_AUTO_LOAD, SqlDbType.TinyInt,0,obj.AutoLoad)
			};

            return dbParams;
            #endregion
        }

        public static bool Save(OptionsEntity obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetOptionsParams(obj);

                if (obj.OptionId == 0)
                {
                    obj.OptionId = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Options_InsertUpdate", dbParams));
                    return obj.OptionId > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Options_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Options-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static List<OptionsEntity> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<OptionsEntity> result = new List<OptionsEntity>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Product_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        OptionsEntity model = new OptionsEntity();
                        FillEntityFrom(reader, model);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Options-->Search-->" + e.ToString());
                ExceptionMessageDB.Record(ex);
            }
            return result;
            #endregion
        }

        public static bool DeleteByOptionId(long optionId)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_OPTION_ID, SqlDbType.BigInt, 0, optionId) };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Options_DeleteByOptionId", dbParams) > 0 ? true : false;

            }

            catch (Exception e)
            {
                Exception ex = new Exception("Options-->DeleteByOptionId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static OptionsEntity GetByOptionId(long optionId)
        {
            #region
            SqlDataReader reader = null;
            OptionsEntity obj = new OptionsEntity();

            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_OPTION_ID, SqlDbType.BigInt, 0, optionId) };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Options_GetByOptionId", dbParams);

                if (reader.Read())
                {
                    FillEntityFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Options-->GetByOptionId-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static OptionsEntity GetByOptionName(string optionName)
        {
            #region
            SqlDataReader reader = null;
            OptionsEntity obj = new OptionsEntity();
            obj.Value = "";
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_NAME, SqlDbType.VarChar, 0, optionName) };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Options_GetByOptionName", dbParams);

                if (reader.Read())
                {
                    FillEntityFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Options-->GetByOptionName-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static bool DeleteByOptionName(string optionName)
        {
            #region
            try
            {
                SqlParameter[] dbParams = new SqlParameter[] { SQLHelper.MakeParam(PARAM_NAME, SqlDbType.VarChar, 0, optionName) };

                return SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Options_DeleteByOptionName", dbParams) > 0 ? true : false;

            }

            catch (Exception e)
            {
                Exception ex = new Exception("Options-->DeleteByOptionName-->" + e.Message);

                ExceptionMessageDB.Record(ex);
            }

            return false;
            #endregion
        }

        public static List<OptionsEntity> GetAll()
        {
            #region
            List<OptionsEntity> result = new List<OptionsEntity>();
            try
            {
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Options_GetAll", null))
                {
                    while (reader.Read())
                    {
                        OptionsEntity model = new OptionsEntity();
                        FillEntityFrom(reader, model);
                        result.Add(model);
                    }
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Options-->GetAll-->" + e.ToString());
                ExceptionMessageDB.Record(ex);
            }
            return result;
            #endregion
        }

    }
}
